//新規書籍登録内容をデータベースに登録するクラス

package book;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Map;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import common.DBManager;

public class WriteServlet extends HttpServlet {

	Statement smt 		= null;
	Connection con 		= null;
	ResultSet rs 		= null;


	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

//　入力パラメータの文字エンコーディング
		request.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=UTF-8");

//　入力パラメータの取得
		String title 			= (String)request.getParameter("title");
		String genre 			= (String)request.getParameter("genre");
		String publisher 		= (String)request.getParameter("publisher");
		String writer 			= (String)request.getParameter("writer");
		String content 			= (String)request.getParameter("content");
		String publication_date = (String)request.getParameter("publication_date");
		String isbn 			= (String)request.getParameter("isbn");
		String picture			= (String)request.getParameter("picture");

		String duplicate		=(String)request.getParameter("duplicate");
		boolean duplicate_check = true;

//		遷移先を書籍検索結果に変更	2013.10.11	若林
//		String path = "/book/userBookEntry.jsp";
		String path = "/book/SearchServlet";
//		ここまで

		//セッション情報の、担当者IDと担当者名を読み込む
		HttpSession session 	= request.getSession();
		session.getAttribute("user_id");
		session.getAttribute("username");

		String manager_id = (String) session.getAttribute("user_id");

		try {

			//　ジャンルIDを取得
			int genre_id 		= Genre_id(genre);
			//　出版者IDを取得
			int publisher_id 	= Publisher_id(publisher);
			//　最終更新IDを取得
			int update_id 		= Update_ID(manager_id);

			//	問い合わせ（SQL）を実行する準備
			con 	= DBManager.getConnection();
			smt  	= con.createStatement();

			String  isql 		= "insert into  books(ID,TITLE,WRITER,GENRE_ID,PUBLISHER_ID,PUBLICATION_DATE,ISBN,CONTENT,PICTURE,DELETE_FLAG,OPEN_FLAG,RENTAL_ID,NEWEST_ID) " +
										"value(null,?,?,?,?,?,?,?,?,0,0,null,?)";

			PreparedStatement ist = con.prepareStatement(isql);

			ist.setString(1, title);
			ist.setString(2, writer);
			ist.setInt(3, genre_id);
			ist.setInt(4, publisher_id);
			ist.setString(5, publication_date);
			ist.setString(6, isbn);
			ist.setString(7, content);
			ist.setString(8, picture);
			ist.setInt(9, update_id);

			ist.executeUpdate();

			rs.close();

//			一覧へ遷移後のメッセージを追加	2013.10.17	矢野
			request.setAttribute("msg", "登録しました");

		}
		catch (SQLException e){
//=====▼======	一覧へ遷移後のメッセージを追加	2013.10.17	矢野=====▼======
		//	throw new ServletException(e);
			//　エラーメッセージを格納するArrayListの生成
			ArrayList<String> error_message = new ArrayList<String>();
			error_message.add("発行年月日が存在しません");
			String publication_date_redline = "red_frame";
			request.setAttribute( "error_message" , error_message );
	     	request.setAttribute( "publication_date_redline" , publication_date_redline );
			path = "/book/userBookEntry.jsp";
//=====▲======	一覧へ遷移後のメッセージを追加	2013.10.17	矢野=====▲======
		}
		finally{
			if(smt != null){
				try{
					smt.close();
				}
				catch(Exception e){
				}
				if(con != null){
					try{
						con.close();
					}
					catch(Exception e){
					}
				}
			}
		}
		//	登録後に書籍検索結果の最終頁を表示するためにページ番号を書き換える		2013.10.11	若林
		//	セッション情報からパラメータを取得
		HttpSession s = request.getSession();
		@SuppressWarnings("unchecked")
		Map<String, String[]> m = (Map<String, String[]>)s.getAttribute("BookSerchParameter");
		//	ページ番号を９９９９９９に書きかえる
		String[] pages = {"999999"};
		//	パラメータをセッション情報に保存
		m.put("page", pages);
		s.removeAttribute("BookSerchParameter");
		s.setAttribute("BookSerchParameter", m);
		//		ここまで

		//　書籍検索結果画面へ遷移
		RequestDispatcher dispatcher = request.getRequestDispatcher(path);
		dispatcher.include(request, response);
	}

	public int Update_ID(String manager_id) throws SQLException {
		int udate_id = 0;
		con = DBManager.getConnection();
		smt = con.createStatement();
		//　updateテーブルに最終更新日時と更新者を作成して、その最終更新IDを取得
		String sql_newest = "insert into updates(NEWEST_ID,UPDATE_DATE,MANAGER_ID) " +
							"value(null,?,?)";
		PreparedStatement ist_newest = con.prepareStatement(sql_newest);

		String time = Zikoku();

		ist_newest.setString(1,time);
		ist_newest.setString(2,manager_id);

		ist_newest.executeUpdate();
		sql_newest = "select * from updates  where update_date = '" + time + "' and manager_id = '" +  manager_id + "'";

		rs = smt.executeQuery(sql_newest);
		while(rs.next())
		{
			udate_id = rs.getInt("NEWEST_ID");
		}

		rs.close();
		smt.close();
		con.close();

		return udate_id;

	}

	//　時間の取得
	public String Zikoku(){
		      Calendar calendar = Calendar.getInstance(); //インスタンス化

		        int year = calendar.get(Calendar.YEAR);
			    int month = calendar.get(Calendar.MONTH) + 1;
			    int day = calendar.get(Calendar.DATE);
			    int hour =calendar.get(Calendar.HOUR_OF_DAY);
			    int minute = calendar.get(Calendar.MINUTE);
			    int second = calendar.get(Calendar.SECOND);

			    String time = (year + "-" + month + "-" + day + "  " + hour + ":" + minute + ":" + second);

			    return time;

		   }
	//　ジャンルIDを取得する
	public int Genre_id(String genre) throws SQLException {
		int genre_id 		= 0;
		con = DBManager.getConnection();
		smt = con.createStatement();

		// 書籍情報取得SQL文
		String sql = "select * from genres  where NAME = '" + genre + "'";
		ResultSet rs = smt.executeQuery(sql);

		while(rs.next())
		{
		genre_id = rs.getInt("ID");
		}
		rs.close();
		smt.close();
		con.close();

		return genre_id;
	}

	//　出版者IDを取得する
	public int Publisher_id(String publisher) throws SQLException{

		int publisher_id 		= 9999999;
		con = DBManager.getConnection();
		smt = con.createStatement();

		// 書籍情報取得SQL文
		//　入力された出版者のIDをpublishersテーブルから取得
		String sql = "select * from publishers  where NAME = '" + publisher + "'";

		String sqlCnt = "select count(*) as cnt from publishers where NAME = '" + publisher + "'";

		ResultSet rsCnt = smt.executeQuery(sqlCnt);

		if(rsCnt.next()){
			int coung = rsCnt.getInt("cnt");
			rsCnt.close();
			//　出版者がDBになかった場合新しく作成すしてIDを取得
			if(coung == 0){
				String sql_max = "select max(ID) from publishers";
				ResultSet rs_max = smt.executeQuery(sql_max);

				while(rs_max.next())
				{
					publisher_id = rs_max.getInt("MAX(ID)") + 1;
				}

				String sql_new_publisher = "insert into publishers(ID,NAME) " +
						"value(?,?)";
				PreparedStatement ist_newest = con.prepareStatement(sql_new_publisher);

				ist_newest.setInt(1,publisher_id);
				ist_newest.setString(2,publisher);
				ist_newest.executeUpdate();
				rs_max.close();

			}else{
				ResultSet rs = smt.executeQuery(sql);
				while(rs.next())
				{
				publisher_id = rs.getInt("ID");
				}

			}

			smt.close();
			con.close();
		}
		return publisher_id;
}

	public boolean Duplicate(String isbn) throws SQLException{

		boolean duplicate = false;

	String isbn_sql = "select count(*) as cnt from books where ISBN = '"+isbn + "'";

	con = DBManager.getConnection();
	smt  = con.createStatement();

	ResultSet rs   = smt.executeQuery(isbn_sql);

	if(rs.next()){
		int coung = rs.getInt("cnt");
		if(coung != 0);
		duplicate = true;
	}
	return duplicate;
	}
}
